package com.source.lightfowl.basedata.dao;

import com.source.lightfowl.VO.PoultrySalesVO;
import com.source.lightfowl.basedata.entity.PoultryCategory;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.math.BigInteger;
import java.util.List;

/**
 * @Description:
 * @Author: Faink
 * @CreateTime: 2020/9/11 16:41
 */
public interface PoultrySalesDao extends JpaRepository<PoultryCategory,Integer>, JpaSpecificationExecutor<PoultryCategory> {
    @Query(value = "SELECT " +
            "any_value(poultry_category.id) AS `poultryId`," +
            "any_value(labeling_record.LabelingDate) AS labelingDate," +
            "any_value(labeling_record.barcodeMachineId) AS barcodeMachineId, " +
            "any_value(labeling_record.barcodeNumber) AS barcodeNumber, " +
            "poultry_category.`name` AS `poultryName`, " +
            "any_value(sales_terminal.`id`) AS `sales_terminal_id`," +
            "any_value(sales_terminal.addr) AS `salesAddr`," +
            "any_value(sales_terminal.`name`) AS `salesTerminalName`," +
            "WEEK(labeling_record.LabelingDate) as `week`," +
            "COUNT(poultry_category.`name`) AS `salesSum`," +
            "slaughter_house.addr AS `houseAddr`," +
            "slaughter_house.`name` AS `houseName`," +
            "slaughter_house.latitude AS `houseLatitude`," +
            "slaughter_house.longitude AS `houseLongitude` " +
            "FROM labeling_record " +
            "LEFT JOIN poultry_category ON labeling_record.poultryCategoryId=poultry_category.id " +
            "LEFT JOIN slaughter_house ON poultry_category.slaughterHouseId=slaughter_house.id " +
            "LEFT JOIN sales_terminal ON labeling_record.salesTerminalId=sales_terminal.id " +
            "WHERE poultry_category.slaughterHouseId=?1 GROUP BY `week`, poultryName",
            nativeQuery = true
    )
    List<Object> findAllSalesByHouse(int id);

    @Query(value = "SELECT " +
            "any_value(poultry_category.id) AS `poultryId`," +
            "any_value(labeling_record.LabelingDate) AS labelingDate," +
            "any_value(labeling_record.barcodeMachineId) AS barcodeMachineId, " +
            "any_value(labeling_record.barcodeNumber) AS barcodeNumber, " +
            "poultry_category.`name` AS `poultryName`, " +
            "any_value(sales_terminal.`id`) AS `sales_terminal_id`," +
            "any_value(sales_terminal.addr) AS `salesAddr`," +
            "any_value(sales_terminal.`name`) AS `salesTerminalName`," +
            "WEEK(labeling_record.LabelingDate) as `week`," +
            "COUNT(poultry_category.`name`) AS `salesSum`," +
            "slaughter_house.addr AS `houseAddr`," +
            "slaughter_house.`name` AS `houseName`," +
            "slaughter_house.latitude AS `houseLatitude`," +
            "slaughter_house.longitude AS `houseLongitude` " +
            "FROM labeling_record " +
            "LEFT JOIN poultry_category ON labeling_record.poultryCategoryId=poultry_category.id " +
            "LEFT JOIN slaughter_house ON poultry_category.slaughterHouseId=slaughter_house.id " +
            "LEFT JOIN sales_terminal ON labeling_record.salesTerminalId=sales_terminal.id " +
            "WHERE poultry_category.id=?1 GROUP BY `week`, poultryName",
            nativeQuery = true
    )
    List<Object> findAllSalesById(String id);

    @Query(
            nativeQuery = true,
            value = "SELECT" +
                    " barcodeMachineId," +
                    " LabelingDate," +
                    " barcodeNumber," +
                    " p.`name` AS poultryName," +
                    " s.addr AS salesAddr," +
                    " s.`name` AS salesTerminalName" +
                    " FROM labeling_record l" +
                    " LEFT JOIN" +
                    " poultry_category p ON l.poultryCategoryId=p.id" +
                    " LEFT JOIN" +
                    " sales_terminal s ON l.salesTerminalId=s.id" +
                    " WHERE l.barcodeMachineId=?1"
    )
    List<Object> findAllRecordByBarcodeId(int id);

    @Query(
            nativeQuery = true,
            value = "SELECT `name` " +
                    "FROM slaughter_house " +
                    "WHERE id=" +
                        "(SELECT sid " +
                        "FROM " +
                            "(SELECT COUNT(p.parentId) AS `sale_count`,p.parentId,p.slaughterHouseId AS `sid` FROM " +
                                "(SELECT poultryCategoryId " +
                                "FROM labeling_record " +
                                "WHERE YEAR(LabelingDate)=YEAR(NOW()) " +
                                "AND MONTH(LabelingDate)=MONTH(NOW())-3) AS lab " +
                            "LEFT JOIN poultry_category p ON p.id=lab.poultryCategoryId " +
                            "WHERE p.parentId=(?1) " +
                            "GROUP BY p.parentId,p.slaughterHouseId " +
                            "ORDER BY `sale_count` DESC LIMIT 1) " +
                        "AS `maxone`)"
    )
    String findMaxSaleOfParentHouseThreeMonthsAgo(String parentId);

    @Query(
            nativeQuery = true,
            value = "SELECT `name` " +
                    "FROM slaughter_house " +
                    "WHERE id=" +
                        "(SELECT sid " +
                        "FROM " +
                            "(SELECT COUNT(p.parentId) AS `sale_count`,p.parentId,p.slaughterHouseId AS `sid` FROM " +
                                "(SELECT poultryCategoryId " +
                                "FROM labeling_record " +
                                "WHERE YEAR(LabelingDate)=YEAR(NOW()) " +
                                "AND MONTH(LabelingDate)=MONTH(NOW())-2) AS lab " +
                            "LEFT JOIN poultry_category p ON p.id=lab.poultryCategoryId " +
                            "WHERE p.parentId=(?1) " +
                            "GROUP BY p.parentId,p.slaughterHouseId " +
                            "ORDER BY `sale_count` DESC LIMIT 1) " +
                        "AS `maxone`)"
    )
    String findMaxSaleOfParentHouseTwoMonthsAgo(String parentId);

    @Query(
            nativeQuery = true,
            value = "SELECT `name` " +
                    "FROM slaughter_house " +
                    "WHERE id=" +
                        "(SELECT sid " +
                        "FROM " +
                            "(SELECT COUNT(p.parentId) AS `sale_count`,p.parentId,p.slaughterHouseId AS `sid` FROM " +
                                "(SELECT poultryCategoryId " +
                                "FROM labeling_record " +
                                "WHERE YEAR(LabelingDate)=YEAR(NOW()) " +
                                "AND MONTH(LabelingDate)=MONTH(NOW())-1) AS lab " +
                            "LEFT JOIN poultry_category p ON p.id=lab.poultryCategoryId " +
                            "WHERE p.parentId=(?1) " +
                            "GROUP BY p.parentId,p.slaughterHouseId " +
                            "ORDER BY `sale_count` DESC LIMIT 1) " +
                        "AS `maxone`)"
    )
    String findMaxSaleOfParentHouseOneMonthsAgo(String parentId);

    @Query(
            nativeQuery = true,
            value ="SELECT " +
                    "any_value(to_market.province),any_value(to_market.city)," +
                    "any_value(to_market.district),any_value(to_market.addr)," +
                    "any_value(to_market.name),COUNT(labeling_record.id) AS `saleCount` " +
                    "FROM labeling_record " +
                    "LEFT JOIN to_market " +
                    "ON to_market.id=labeling_record.toMarkId " +
                    "WHERE poultryCategoryId IN" +
                        "(SELECT id FROM `poultry_category` " +
                        "WHERE slaughterHouseId=(?1)) " +
                    "AND LabelingDate>=DATE_SUB(CURDATE(),INTERVAL 1 MONTH) " +
                    "AND LabelingDate<CURDATE() " +
                    "GROUP BY toMarkId " +
                    "ORDER BY `saleCount` DESC"
    )
    List<Object> findMarketSortBySales(String houseId);

    @Query(
            nativeQuery = true,
            value ="SELECT " +
                    "any_value(to_market.province),any_value(to_market.city)," +
                    "any_value(to_market.district),any_value(to_market.addr)," +
                    "any_value(to_market.`name`)," +
                    "COUNT(labeling_record.id) AS `saleCount` " +
                    "FROM labeling_record " +
                    "LEFT JOIN to_market ON to_market.id=labeling_record.toMarkId " +
                    "WHERE poultryCategoryId IN" +
                        "(SELECT id " +
                        "FROM `poultry_category` " +
                        "WHERE slaughterHouseId=?1 " +
                        "AND parentId=?2) " +
                    "AND LabelingDate>=DATE_SUB(CURDATE(),INTERVAL 1 MONTH) " +
                    "AND LabelingDate<CURDATE() " +
                    "GROUP BY toMarkId " +
                    "ORDER BY `saleCount` DESC"

    )
    List<Object> findMarketSortByParentSales(String houseId, String parentId);

    @Query(
            nativeQuery = true,
            value = "SELECT " +
                    "COUNT(labeling_record.id) AS `saleCount`," +
                    "any_value(to_market.`name`)," +
                    "any_value(to_market.marketLongitude)," +
                    "any_value(to_market.marketLatitude) FROM `labeling_record` " +
                    "LEFT JOIN to_market " +
                    "ON to_market.id=labeling_record.toMarkId " +
                    "WHERE poultryCategoryId IN" +
                        "(SELECT id FROM poultry_category " +
                        "WHERE slaughterHouseId=?1) " +
                    "AND toMarkId IN" +
                        "(SELECT DISTINCT toMarkId FROM labeling_record " +
                        "WHERE poultryCategoryId IN" +
                            "(SELECT id FROM poultry_category " +
                        "WHERE slaughterHouseId=?1)) " +
                    "GROUP BY toMarkId"
    )
    List<Object> findSaleByHouseAndMarket(String houseId);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(*) " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN" +
                        "(SELECT id FROM poultry_category " +
                        "WHERE slaughterHouseId=?1)"
    )
    BigInteger findHouseSaleCount(String houseId);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(id) FROM labeling_record " +
                    "WHERE poultryCategoryId IN" +
                    "(SELECT id FROM poultry_category " +
                    "WHERE slaughterHouseId=?1 " +
                    "AND parentId=?2) " +
                    "GROUP BY parentId"
    )
    BigInteger findCountByHouseIdAndParentId(String houseId, String parentId);

    @Query(
            nativeQuery = true,
            value = "SELECT COUNT(id) FROM labeling_record " +
                    "WHERE poultryCategoryId IN" +
                    "(SELECT id FROM poultry_category " +
                    "WHERE slaughterHouseId=?1 " +
                    "AND parentId=?2) " +
                    "AND YEAR(LabelingDate)=YEAR(CURDATE())"
    )
    BigInteger findCountByHouseIdAndParentIdThisYear(String houseId, String parentId);
}
